﻿using System;
using System.Collections.Generic;
using System.Globalization;
using System.Linq;
using System.Web;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using EShop.Common;
using EShop.BussinessObjects;
using MySql.Data.MySqlClient;

namespace EShop.DataAccess
{
    public class CDAUserBonus
    {

        public static Boolean Delete(UserBonus userBonus, out string errMsg)
        {
            SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ToString());
            con.Open();
            errMsg = string.Empty;
            string _cmdText = " DELETE userBonus " +
                              " WHERE ID = " + userBonus.ID.ToString();
            try
            {
                SqlCommand cmd = new SqlCommand(_cmdText, con);
                cmd.ExecuteNonQuery();

                con.Close();
                return true;
            }
            catch (Exception e)
            {
                errMsg = e.Message;
                con.Close();
                return false;
            }
        }

        public static Boolean Update(UserBonus userBonus, out string errMsg)
        {
            MySqlConnection con = new MySqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ToString());
            con.Open();
            errMsg = string.Empty;
            string _cmdText = " UPDATE userbonus SET " +
                              "  CompanyID = " + ((string.IsNullOrEmpty(userBonus.Company.ID.ToString()) || (userBonus.Company.ID == (int)cnstDatabase.DBOperationResult.NullValue)) ? "NULL" : userBonus.Company.ID.ToString()) +
                              " ,UserID = " + ((string.IsNullOrEmpty(userBonus.User.ID.ToString()) || (userBonus.User.ID == (int)cnstDatabase.DBOperationResult.NullValue)) ? "NULL" : userBonus.User.ID.ToString()) +
                              " ,Cnt = " + ((string.IsNullOrEmpty(userBonus.Cnt.ToString()) || (userBonus.Cnt == (int)cnstDatabase.DBOperationResult.NullValue)) ? "NULL" : userBonus.Cnt.ToString()) +
                              " WHERE ID = " + userBonus.ID.ToString();
            try
            {
                MySqlCommand cmd = new MySqlCommand(_cmdText, con);
                cmd.ExecuteNonQuery();

                con.Close();
                return true;
            }
            catch (Exception e)
            {
                errMsg = e.Message;
                con.Close();
                return false;
            }
        }

        public static Boolean Insert(UserBonus userBonus, out string errMsg)
        {
            MySqlConnection con = new MySqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ToString());
            con.Open();
            errMsg = string.Empty;
            string _cmdText = " INSERT INTO userbonus (CompanyID, UserID, Cnt) " +
                              " VALUES(" + ((string.IsNullOrEmpty(userBonus.Company.ID.ToString()) || (userBonus.Company.ID == (int)cnstDatabase.DBOperationResult.NullValue)) ? "NULL" : userBonus.Company.ID.ToString()) +
                              " ," + ((string.IsNullOrEmpty(userBonus.User.ID.ToString()) || (userBonus.User.ID == (int)cnstDatabase.DBOperationResult.NullValue)) ? "NULL" : userBonus.User.ID.ToString()) +
                              " ," + ((string.IsNullOrEmpty(userBonus.Cnt.ToString()) || (userBonus.Cnt == (int)cnstDatabase.DBOperationResult.NullValue)) ? "NULL" : userBonus.Cnt.ToString()) +
                              ")";
            try
            {
                MySqlCommand cmd = new MySqlCommand(_cmdText, con);
                cmd.ExecuteNonQuery();

                _cmdText = " SELECT last_insert_id(); ";
                cmd = new MySqlCommand(_cmdText, con);
                int _id = Convert.ToInt32(cmd.ExecuteScalar());
                userBonus.ID = _id;

                con.Close();
                return true;
            }
            catch (Exception e)
            {
                errMsg = e.Message;
                con.Close();
                return false;
            }
        }

        public static List<UserBonus> Select(UserBonus userBonus)
        {
            MySqlConnection con = new MySqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ToString());
            con.Open();

            string _cmdText =
            " SELECT u.* " +
            " FROM userBonus u " +
            " WHERE ((u.ID = " + userBonus.ID.ToString() + ") OR (" + userBonus.ID.ToString() + " = " + ((int)cnstDatabase.DBOperationResult.ValueIsNotEffective).ToString() + ")) " +
            "   AND ((u.CompanyID = " + userBonus.Company.ID.ToString() + ") OR (" + userBonus.Company.ID.ToString() + " = " + ((int)cnstDatabase.DBOperationResult.ValueIsNotEffective).ToString() + ")) " +
            "   AND ((u.UserID = " + userBonus.User.ID.ToString() + ") OR (" + userBonus.User.ID.ToString() + " = " + ((int)cnstDatabase.DBOperationResult.ValueIsNotEffective).ToString() + ")) " +
            "ORDER BY u.ID";

            MySqlCommand cmd = new MySqlCommand(_cmdText, con);
            MySqlDataAdapter dataAdapter = new MySqlDataAdapter(cmd);
            DataTable tbl = new DataTable();
            dataAdapter.Fill(tbl);

            List<UserBonus> _userBonusList = new List<UserBonus>();

            foreach (DataRow row in tbl.Rows)
            {
                UserBonus _userBonus = new UserBonus();

                _userBonus.ID = (row["ID"].ToString() == "" ? ((int)cnstDatabase.DBOperationResult.NullValue) : Convert.ToInt32(row["ID"]));

                _userBonus.User = new User();
                _userBonus.User.ID = (row["UserID"].ToString() == "" ? ((int)cnstDatabase.DBOperationResult.NullValue) : Convert.ToInt32(row["UserID"]));

                _userBonus.Company = new Company();
                _userBonus.Company.ID = (row["CompanyID"].ToString() == "" ? ((int)cnstDatabase.DBOperationResult.NullValue) : Convert.ToInt32(row["CompanyID"]));

                _userBonus.Cnt = (row["Cnt"].ToString() == "" ? ((int)cnstDatabase.DBOperationResult.NullValue) : Convert.ToInt32(row["Cnt"]));

                _userBonusList.Add(_userBonus);
            }

            con.Close();
            return _userBonusList;
        }
    }
}
